#生成方便直接复制的台号违规次数和违规时长的excel

import  pandas as pd
import numpy as np
import xlsxwriter
import  xlwt

excel1 = pd.read_excel('daydaytable.xls')

len1 = excel1.shape[0]

temp_data = excel1.groupby('台号')['违规时长'].agg([len,np.sum])


# print(temp_data)
# print(len1)


taihao_arry = [
    21,22,23,24,25,26,26,28,  #【0：8】
    31,32,33,34,35,36,37,38,  #[8:16]
    41,42,43,44,45,46,47,48,  #[16:24]
    51,52,53,54,55,56,57,58,  #[24:32]
    61,62,63,64,65,66,67,68,  #[32:40]
    71,72,73,74,75,76,77,78,  #[40:48]
    91,92,93,94,95,96,97,98,  #[48:56]
]

# print('{}{}'.format('范围测试 21-28',taihao_arry[0:8]))
data_arry = []

# print(temp_data.loc[21].values)  #[ 4  7040]
# print(list(temp_data.loc[21].values)) #[4, 7040]
#


for i in taihao_arry:
    try:
        data_arry.append(list(temp_data.loc[i].values))
    except:
        data_arry.append([0,0])
    # data_arry.append([i, list(temp_data.loc[i])])
#
# print(data_arry)



workbook = xlwt.Workbook()

worksheet = workbook.add_sheet('test')

# worksheet.write(0,1,'A1data')

print(type(data_arry[1][0]))

col_num = 0


# for i in taihao_arry:
#
#
#         worksheet.write(0,col_num,i)
#         worksheet.write(1,col_num,int(data_arry[i-43][0]))#其中data_arry为<class 'numpy.int64'>
#         worksheet.write(2,col_num,int(data_arry[i-43][1]))
#         col_num += 1

##构建响水  91-98
for i in range(8):
    worksheet.write(0,i,91+i)
    worksheet.write(1,i,int(data_arry[48+i][0]))
    worksheet.write(2,i,int(data_arry[48+i][1]))


##构建滨海  41-48
for i in range(8):
    worksheet.write(3,i,41+i)
    worksheet.write(4,i,int(data_arry[16+i][0]))
    worksheet.write(5,i,int(data_arry[16+i][1]))


##构建阜宁  71-78
for i in range(8):
    worksheet.write(6,i,71+i)
    worksheet.write(7,i,int(data_arry[40+i][0]))
    worksheet.write(8,i,int(data_arry[40+i][1]))


##构建射阳  21-28
for i in range(8):
    worksheet.write(9,i,21+i)
    worksheet.write(10,i,int(data_arry[i][0]))
    worksheet.write(11,i,int(data_arry[i][1]))

##构建建湖  61-68

for i in range(8):
    worksheet.write(12,i,61+i)
    worksheet.write(13,i,int(data_arry[32+i][0]))
    worksheet.write(14,i,int(data_arry[32+i][1]))

##构建东台  51-58
for i in range(8):
    worksheet.write(15,i,51+i)
    worksheet.write(16,i,int(data_arry[24+i][0]))
    worksheet.write(17,i,int(data_arry[24+i][1]))
##构建大丰  31-38



for i in range(8):
    worksheet.write(18,i,31+i)
    worksheet.write(19,i,int(data_arry[8+i][0]))
    worksheet.write(20,i,int(data_arry[8+i][1]))

workbook.save('excelwrite.xls')


